package br.com.werp.regras.contabilidade.integracao;

import br.com.werp.regras.geral.Debuger;
import br.com.werp.regras.geral.integracao.MasterDAO;
import br.com.werp.regras.geral.util.Constantes;
import br.com.werp.regras.geral.util.Dia;
import br.com.werp.regras.geral.vos.tabelas.Lote;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import java.util.List;
import java.util.ResourceBundle;


/**
 * @author Sobreira
 * Criado em 16/05/2005
 *
 */
public class LoteDAO extends MasterDAO {
    private static LoteDAO LoteDAO;
    private static final String APAGAR_LOTE = "APAGAR_LOTE";
    private static final String MAX_SERIAL_LOTE = "MAX_SERIAL_LOTE";
    private static final String SERIAL = "SERIAL";

    public LoteDAO(String banco) throws Exception {
        super(banco);
    }

    public static LoteDAO getLoteDAO() throws Exception {
        if (LoteDAO == null) {
            ResourceBundle bundle = ResourceBundle.getBundle(Constantes.CONFIG);
            LoteDAO = new LoteDAO(bundle.getString(Constantes.BANCO));
        }

        return LoteDAO;
    }

    public void atualizaLoteData(Object trx, Lote lote)
        throws Exception {
        Connection connection = validarTransacao(trx);
        atualizarVO(connection, lote, new String[] { "lote_id" },
            new String[] { "lote_pessoa", "lote_documento" });
    }

    public boolean deletarlote(Object tranx, int loteId)
        throws Exception {
        Connection conn = validarTransacao(tranx);
        String query;
        PreparedStatement statement;

        query = getQuery(APAGAR_LOTE);
        statement = conn.prepareStatement(query);
        setInt(1, statement, loteId);

        return statement.execute();
    }

    public int maxSerialLote(Object key, Dia dia) throws Exception {
        return maxSerialLote(dia);
    }

    public int maxSerialLote(Dia dia) throws Exception {
        Connection connection = getConexao();

        String query = getQuery(MAX_SERIAL_LOTE);
        query = query.replaceAll(P01, dia.getDataBancoAnoMes());

        try {
            connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
            beginTrans(connection);

            PreparedStatement preparedStatement = connection.prepareStatement(query,
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            Debuger.debugar(preparedStatement, getClass());

            ResultSet set = preparedStatement.executeQuery();
            int retorno = 0;

            if (set.next()) {
                retorno = set.getInt(SERIAL);
                set.close();

                return retorno;
            }

            commitTrans(connection);

            return retorno;
        } catch (Exception e) {
            abortarTransacao(connection);
            throw e;
        } finally {
            liberaConeccao(connection);
        }
    }

    public void inserirLoteSemDocumentoPessoa(Object tranx, Lote lote)
        throws Exception {
        Connection conn = validarTransacao(tranx);
        String[] excluidas = { "LOTE_DOCUMENTO", "LOTE_PESSOA" };
        inserirVO(conn, lote, excluidas);
    }

    public void inserirLote(Object tranx, Lote lote) throws Exception {
        Connection conn = validarTransacao(tranx);
        inserirVO(conn, lote);
    }

    public void excluirLote(Object key, Lote lote) throws Exception {
        Connection connection = validarTransacao(key);
        String query = getQuery(APAGAR_LOTE);
        PreparedStatement statement = connection.prepareStatement(query);
        setInt(1, statement, lote.getLote_id());
        Debuger.debugar(statement, getClass());
        statement.executeUpdate();
    }

    public List pesquisarLote(Lote lote) throws Exception {
        Connection connection = getConexao();

        try {
            return pesquisarVO(connection, lote);
        } finally {
            liberaConeccao(connection);
        }
    }

    public List pesquisarLote(Lote lote, Dia inicio, Dia fim)
        throws Exception {
        Connection connection = getConexao();

        try {
            String where = "";

            if (inicio != null) {
                where += (" and lote_data >= " + inicio.getDataBanco());
            }

            if (fim != null) {
                where += (" and lote_data <= " + fim.getDataBanco23h());
            }

            return pesquisarVO(connection, lote, where);
        } finally {
            liberaConeccao(connection);
        }
    }
}
